﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ForkCarRelationManager.db;
using System.Data;

namespace ForkCarRelationManager.Business
{
    class ClientBI
    {
        DataPaging ClientPage;
        DataSet ClientDataSet;
        //实例化数据操作类
        dbOperate oprate = new dbOperate();
        int pagecount;

        //构造函数
        public DataPaging GetClientPage(int pagecount)
        {
            this.pagecount = pagecount;
            String filed = "(select sum(1) from (select c.ID, c.ClientName,c.Location,s.ScompanyName,c.ContactName,c.ContactPosition,c.ContactPhone,c.LoadTime,c.SalesmanName,c.ID as clentId from ClientInfor c ,ScompanyInfor s where c.ScompanyId = s.Id ) t where t.ID<=client.ID ) as 序号,client.ClientName,client.Location,company.ScompanyName,client.ContactName,client.ContactPosition,client.ContactPhone,client.LoadTime,client.SalesmanName,client.ID as Id";
            ClientPage = new DataPaging("ClientInfor client,ScompanyInfor company", pagecount, filed, "client.ScompanyId = company.Id", "client");
            return ClientPage;
        }
        /*
       * 获取总数据量
       * */
        public int GetDatacount(DataPaging datapage)
        {
            int datacount = 0;
            if (datapage != null)
            {
                datacount = datapage.Getcount();
            }
            return datacount;
        }

        /*****************************************以上方法用于分页******************************************/
        /*
       * 初始化公司
       * */
        public DataTable initCompany()
        {
           
            //查找所有分公司作为数据源
            String sqlCompany = "select s.ID,s.ScompanyName from ScompanyInfor as s";
            DataTable companyTable = oprate.GetDate(sqlCompany);
            return companyTable;
        }

        /*
       * 初始化客户列表数据
       * */
        public DataTable initClient()
        {
            //初始化数据列表
            String SQL = "SELECT (select sum(1) from ClientInfor where ID<=client.ID) as ID,client.ClientName,client.Location,company.ScompanyName,client.ContactName,client.ContactPosition,client.ContactPhone,client.LoadTime,client.SalesmanName,client.ID as clentId From  ClientInfor as client left join ScompanyInfor as company on client.ScompanyId = company.Id";
            DataTable dt = oprate.GetDate(SQL);
            return dt;
        }

        /*
         * 按照条件搜索(暂时不用)
         */

        public DataTable searchClient(String clientName,String location,String company,DateTime beginTime,DateTime endTime )
        {
            String sql = "SELECT ( select sum(1) from ClientInfor where ID<=client.ID) as ID,client.ClientName,client.Location,company.ScompanyName,client.ContactName,client.ContactPosition,client.ContactPhone,client.LoadTime,client.SalesmanName,client.ID as clentId From  ClientInfor as client left join ScompanyInfor as company on client.ScompanyId = company.Id where 1=1";
            //按照客户匹配查找
            if (clientName.Length != 0)
            {
                //client.ClientName like '*严*' and client.Locationlike '*城阳*' and company.ScompanyName = '田园牧歌'";
                sql = sql + " and client.ClientName like '%" + clientName + "%'";
            }
            //按照区域查找
            if (location.Length != 0)
            {
                //client.ClientName like '*严*' and client.Locationlike '*城阳*' and company.ScompanyName = '田园牧歌'";
                sql = sql + " and client.Location like '%" + location + "%'";
            }
            //按照公司查找
            if (company.Length != 0)
            {
                //client.ClientName like '*严*' and client.Locationlike '*城阳*' and company.ScompanyName = '田园牧歌'";
                sql = sql + " and company.ScompanyName = '" + company + "'";
            }
            //按照录入时间查找
            if (beginTime <= endTime)
            {
                sql = sql + " and LoadTime between #" + beginTime + "# and #" + endTime + "#";
            }
            DataTable dt = oprate.GetDate(sql);
            return dt;
        }

        /*
         * 按照客户名称，区域,公司名称，联系人姓名等模糊搜索
         */
        public DataTable searchWithStr(String str)
        {
            String sql = "SELECT ( select sum(1) from ClientInfor where ID<=client.ID) as ID,client.ClientName,client.Location,company.ScompanyName,client.ContactName,client.ContactPosition,client.ContactPhone,client.LoadTime,client.SalesmanName,client.ID as clentId From  ClientInfor as client left join ScompanyInfor as company on client.ScompanyId = company.Id where 1=1";
            if (str.Length != 0)
            {
               sql = sql + " and client.ClientName like '%"+str+"%' or client.Location like '%"+str+"%' or client.ContactName like '%"+str+"%' or company.ScompanyName like '%"+str+"%'";
                
            }
            DataTable dt = oprate.GetDate(sql);
            return dt;
        }

        /*
        * 按照客户名称，区域,公司名称，联系人姓名等模糊搜索(分页)
        */
        public DataPaging searchWithStr(String str, int pagecount)
        {
            String condition="";
            String conditionFile = "";
            if (str.Length != 0)
            {
                condition += " and (client.ClientName like '%" + str + "%' or client.Location like '%" + str + "%' or client.ContactName like '%" + str + "%' or company.ScompanyName like '%" + str + "%')";
                conditionFile += " and (c.ClientName like '%" + str + "%' or c.Location like '%" + str + "%' or c.ContactName like '%" + str + "%' or s.ScompanyName like '%" + str + "%')";
            }
            String filed = "(select sum(1) from (select c.ID, c.ClientName,c.Location,s.ScompanyName,c.ContactName,c.ContactPosition,c.ContactPhone,c.LoadTime,c.SalesmanName,c.ID as clentId from ClientInfor c ,ScompanyInfor s where c.ScompanyId = s.Id " + conditionFile + ") t where t.ID<=client.ID ) as 序号,client.ClientName,client.Location,company.ScompanyName,client.ContactName,client.ContactPosition,client.ContactPhone,client.LoadTime,client.SalesmanName,client.ID as Id";
            ClientPage = new DataPaging("ClientInfor client,ScompanyInfor company", pagecount, filed, "client.ScompanyId = company.Id" + condition, "client");
            return ClientPage; 
        }

        /*
        * 按照公司精确查找
        */
        public DataTable searchWithCompany(String CompanyStr)
        {
            String sql = "SELECT ( select sum(1) from ClientInfor where ID<=client.ID) as ID,client.ClientName,client.Location,company.ScompanyName,client.ContactName,client.ContactPosition,client.ContactPhone,client.LoadTime,client.SalesmanName,client.ID as clentId From  ClientInfor as client left join ScompanyInfor as company on client.ScompanyId = company.Id where 1=1";
            if (CompanyStr.Length != 0)
            {
                sql = sql + " and company.ScompanyName = '" + CompanyStr + "'"; 
            }
            DataTable dt = oprate.GetDate(sql);
            return dt;
        }
        /*
       * 按照公司,模糊字段查找(分页)
       */
        public DataPaging searchWithCompany(String CompanyStr,String changStr, int pagecount)
        {
            String condition = "";
            String conditionFile = "";
            if (CompanyStr.Length != 0)
            {
                condition += " and company.ScompanyName = '" + CompanyStr + "'";
                conditionFile += " and s.ScompanyName = '" + CompanyStr + "'";
            }
            if (changStr.Length != 0)
            {
                condition += " and (client.ClientName like '%" + changStr + "%' or client.Location like '%" + changStr + "%' or client.ContactName like '%" + changStr + "%' or company.ScompanyName like '%" + changStr + "%')";
                conditionFile += " and (c.ClientName like '%" + changStr + "%' or c.Location like '%" + changStr + "%' or c.ContactName like '%" + changStr + "%' or s.ScompanyName like '%" + changStr + "%')";
            }
            String filed = "(select sum(1) from (select c.ID, c.ClientName,c.Location,s.ScompanyName,c.ContactName,c.ContactPosition,c.ContactPhone,c.LoadTime,c.SalesmanName,c.ID as clentId from ClientInfor c ,ScompanyInfor s where c.ScompanyId = s.Id " + conditionFile + ") t where t.ID<=client.ID ) as 序号,client.ClientName,client.Location,company.ScompanyName,client.ContactName,client.ContactPosition,client.ContactPhone,client.LoadTime,client.SalesmanName,client.ID as Id";
            ClientPage = new DataPaging("ClientInfor client,ScompanyInfor company", pagecount, filed, "client.ScompanyId = company.Id" + condition, "client",true);
            return ClientPage; 
        }
        /*
       * 按照公司，模糊字段查找
       */
        public DataTable searchWithCompany(String CompanyStr,String changStr)
        {
            String sql = "SELECT ( select sum(1) from ClientInfor where ID<=client.ID) as ID,client.ClientName,client.Location,company.ScompanyName,client.ContactName,client.ContactPosition,client.ContactPhone,client.LoadTime,client.SalesmanName,client.ID as clentId From  ClientInfor as client left join ScompanyInfor as company on client.ScompanyId = company.Id where 1=1";
            if (CompanyStr.Length != 0)
            {
                sql = sql + " and company.ScompanyName = '" + CompanyStr + "'";
            } if (changStr.Length != 0)
            {
                sql = sql + " and (client.ClientName like '%" + changStr + "%' or client.Location like '%" + changStr + "%' or client.ContactName like '%" + changStr + "%' or company.ScompanyName like '%" + changStr + "%')";
            }
            DataTable dt = oprate.GetDate(sql);
            return dt;
        }
        /*
     * 按照公司，模糊字段,时间查找
     */
        public DataTable searchWithCompany(String CompanyStr, String changStr, DateTime beginTime, DateTime endTime)
        {
            String sql = "SELECT DISTINCT  ( select sum(1) from ClientInfor where ID<=client.ID) as ID,client.ClientName,client.Location,company.ScompanyName,client.ContactName,client.ContactPosition,client.ContactPhone,client.LoadTime,client.SalesmanName,client.ID as clentId From (((MaintenRecord as r left join CarInfor as car on r.CarId = car.ID) left join ClientInfor as client on client.ID = car.ClientId) left join ScompanyInfor as company on client.ScompanyId = company.Id) where 1=1";
            if (CompanyStr.Length != 0)
            {
                sql = sql + " and company.ScompanyName = '" + CompanyStr + "'";
            } if (changStr.Length != 0)
            {
                sql = sql + " and (client.ClientName like '%" + changStr + "%' or client.Location like '%" + changStr + "%' or client.ContactName like '%" + changStr + "%' or company.ScompanyName like '%" + changStr + "%')";
            } if (beginTime <= endTime)
            {
                sql = sql + "and MaintenDate between #" + beginTime + "# and #" + endTime + "#";
            }
            DataTable dt = oprate.GetDate(sql);
            return dt;
        }
        /*
        * 按照公司，模糊字段,时间查找(分页)
        */
        public DataPaging searchWithCompany(String CompanyStr, String changStr, DateTime beginTime, DateTime endTime, int pagecount)
        {
           // String sql = "SELECT DISTINCT  ( select sum(1) from ClientInfor where ID<=client.ID) as ID,client.ClientName,iif(client.ClientType = 1,'购买客户',iif(client.ClientType = 2,'租赁客户',iif(client.ClientType = 3,'维修客户','其他'))) as type,client.Location,company.ScompanyName,client.ContactName,client.ContactPosition,client.ContactPhone,client.LoadTime,client.SalesmanName,client.ID as clentId From (((MaintenRecord as r left join CarInfor as car on r.CarId = car.ID) left join ClientInfor as client on client.ID = car.ClientId) left join ScompanyInfor as company on client.ScompanyId = company.Id) where 1=1";
            String condition = "";
            String conditionFile = "";
            if (CompanyStr.Length != 0)
            {
                condition = condition + " and company.ScompanyName = '" + CompanyStr + "'";
                conditionFile = conditionFile + " and d.ScompanyName = '" + CompanyStr + "'";
            } if (changStr.Length != 0)
            {
                condition = condition +" and (client.ClientName like '%" + changStr + "%' or client.Location like '%" + changStr + "%' or client.ContactName like '%" + changStr + "%' or company.ScompanyName like '%" + changStr + "%')";
                conditionFile = conditionFile + " and (c.ClientName like '%" + changStr + "%' or c.Location like '%" + changStr + "%' or c.ContactName like '%" + changStr + "%' or d.ScompanyName like '%" + changStr + "%')";
            } if (beginTime <= endTime)
            {
                condition = condition +" and MaintenDate between #" + beginTime + "# and #" + endTime + "#";
                conditionFile = conditionFile + " and MaintenDate between #" + beginTime + "# and #" + endTime + "#";
            }
            String filed = "( select DISTINCT sum(1) from ( select DISTINCT c.ClientName,c.Location,d.ScompanyName,c.ContactName,c.ContactPosition,c.ContactPhone,c.LoadTime,c.SalesmanName,c.ID as Id From MaintenRecord as a , CarInfor as b,ClientInfor as c,ScompanyInfor as d where a.CarId = b.ID and c.ID = b.ClientId and c.ScompanyId = d.Id " + conditionFile + ") t where t.ID<=client.ID) as 序号,client.ClientName,client.Location,company.ScompanyName,client.ContactName,client.ContactPosition,client.ContactPhone,client.LoadTime,client.SalesmanName,client.ID as Id";
            ClientPage = new DataPaging("MaintenRecord as r , CarInfor as car,ClientInfor as client,ScompanyInfor as company", pagecount, filed, "r.CarId = car.ID and client.ID = car.ClientId and client.ScompanyId = company.ID" + condition, "client",true);
            return ClientPage; 
        }
       

        /*
         * 按照业务时间单独查找 
         */
        public DataTable searchWithTime(DateTime beginTime, DateTime endTime)
        {
            String sql = "SELECT DISTINCT  ( select sum(1) from ClientInfor where ID<=client.ID) as ID,client.ClientName,iif(client.ClientType = 1,'购买客户',iif(client.ClientType = 2,'租赁客户',iif(client.ClientType = 3,'维修客户','其他'))) as type,client.Location,company.ScompanyName,client.ContactName,client.ContactPosition,client.ContactPhone,client.LoadTime,client.SalesmanName,client.ID as clentId From (((MaintenRecord as r left join CarInfor as car on r.CarId = car.ID) left join ClientInfor as client on client.ID = car.ClientId) left join ScompanyInfor as company on client.ScompanyId = company.Id) where 1=1";
            if (beginTime<=endTime)
            {
                sql = sql + "and MaintenDate between #" + beginTime + "# and #" + endTime + "#";
            }
            DataTable dt = oprate.GetDate(sql);
            return dt;
        }

        /*
         * 根据客户Id查找客户资料
         * */
        public DataTable findClientById(String clientId)
        {
            String sql = "SELECT (select sum(1) from ClientInfor where ID<=client.ID) as ID,client.ClientName,client.Location,company.ID as companyId,company.ScompanyName,client.ContactName,client.ContactPosition,client.ContactDiv,client.ContactPhone,client.LoadTime,client.SalesmanName,client.ClientPlace,client.ClientMark,client.ID as clentId From  ClientInfor as client left join ScompanyInfor as company on client.ScompanyId = company.Id where client.ID = " + clientId;
            DataTable dt = oprate.GetDate(sql);
            return dt;
        }

        /*
         * 根据客户id查找客户的车
         * */
        public DataTable findCarsByClientId(String clientId)
        {
            String sql = "select c.Brand,c.Model,c.DeviceNum ,iif(c.Insourcing = 0,'维修车辆',iif(c.Insourcing = 1,'购买车辆',iif(c.Insourcing = 2,'租赁车辆','其他'))) as Insourcing,c.BuyData,c.WorkHour,c.ID from CarInfor as c where c.ClientId = " + clientId;
            DataTable dt = oprate.GetDate(sql);
            return dt;
        }

        /*
         * 根据车Id查找车维修记录
         * 
         * */
        public DataTable findCarMaintenRecordByCarId(String CarId)
        {
            String sql = "select m.MaintenDate,iif(m.Ifmaintain = 0,'维修','保养') as MaintenType,iif(m.Iffree = 0,'保内',iif(m.Iffree = 1,'保外',iif(m.Iffree=2,'返工','其他'))) as ifFree,m.Symptom,m.MaintenContent,m.MaintorName,m.Fear,m.ID from MaintenRecord as m  where m.CarId = " + CarId;
            DataTable dt = oprate.GetDate(sql);
            return dt;
        }

        /*
         * 更新客户资料
         * */
        public bool UpdateClient(String clientName, String location, String scompanyId, String clientPlace, String contactName, String contactPosition, String contactDiv, String contactPhone, String remark,string SalesmanName, int ClientId)
        {
            String sqlClient = "UPDATE ClientInfor set ClientName = '" + clientName + "' , Location = '" + location + "' , ScompanyId ='" + scompanyId + "' , ContactName = '" + contactName + "' , ContactPosition ='" + contactPosition + "' , ContactDiv ='" + contactDiv + "', ContactPhone = '" + contactPhone + "' , ClientPlace = '" + clientPlace + "' , ClientMark ='" + remark + "',SalesmanName='" + SalesmanName + "' where ID=" + ClientId;
            dbOperate oprate = new dbOperate();
            bool mark = oprate.UpdateAndInsert(sqlClient);
            return mark;
        }

        public bool addClient(String clientName,String clientLocation,String companyId,String contactName,String contactPosition,String parts,String contactPhone,String salesmanName,String clientPlace,String clientMark)
        {
            String keys = "( ClientName, Location, ScompanyId, ContactName, ContactPosition, ContactDiv, ContactPhone, LoadTime, SalesmanName, ClientPlace, ClientMark )";
            String sqlClient = "INSERT INTO ClientInfor " + keys + " VALUES ( '" + clientName + "','" + clientLocation + "','" + companyId + "','" + contactName + "','" + contactPosition + "','" + parts + "','" + contactPhone + "',#" + DateTime.Now.ToString() + "#,'" + salesmanName + "','" + clientPlace + "','" + clientMark + "' )";
            return oprate.UpdateAndInsert(sqlClient);
        }
        public int addClientandgetid(String clientName, String clientLocation, String companyId, String contactName, String contactPosition, String parts, String contactPhone, String salesmanName, String clientPlace, String clientMark)
        {
            String keys = "( ClientName, Location, ScompanyId, ContactName, ContactPosition, ContactDiv, ContactPhone, LoadTime, SalesmanName, ClientPlace, ClientMark )";
            String sqlClient = "INSERT INTO ClientInfor " + keys + " VALUES ( '" + clientName + "','" + clientLocation + "','" + companyId + "','" + contactName + "','" + contactPosition + "','" + parts + "','" + contactPhone + "',#" + DateTime.Now.ToString() + "#,'" + salesmanName + "','" + clientPlace + "','" + clientMark + "' )";
            bool ifaddsucs = oprate.UpdateAndInsert(sqlClient);
            int clientid = 0;
            if (ifaddsucs)
            {
                DataTable clientTable = null;
                string sql = "select Id from ClientInfor where ClientName='" + clientName + "' and Location='" + clientLocation + "'and ScompanyId = '" + companyId + "' and ContactName='" + contactName + "' and ContactPosition ='" + contactPosition + "' and ContactDiv ='" + parts + "' and ContactPhone ='" + contactPhone + "' and SalesmanName='" + salesmanName + "' and ClientPlace='" + clientPlace + "' and ClientMark'" + clientMark + "' order by Id desc";
                clientTable = oprate.GetDate("select Id from ClientInfor where ClientName='" + clientName + "' order by Id desc");
                if (clientTable != null)
                {
                    clientid = Convert.ToInt32(clientTable.Rows[0][0].ToString());
                }
                else
                    clientid = 0;
            }
            return clientid;
        }
    }
}
